﻿#region 程序集引用
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Xml;
using System.Reflection;
#endregion


namespace Zhang
{
    namespace SQLServer
    {
        /// <summary>
        /// 查询
        /// </summary>
        public class QueryParam
        {
            /// <summary>
            /// 条件
            /// </summary>
            public string Where { get; set; }
            /// <summary>
            /// 查询参数值
            /// </summary>
            public List<SqlParameter> LstParam { get; set; }
        }

        /// <summary>
        /// SQLServer数据库操作类
        /// </summary>
        public class SQLHelper
        {
            private string connectionString;

            /// <summary>
            /// 设置数据库连接字符串
            /// </summary>
            public string ConnectionString
            {
                get { return connectionString; }
                set { connectionString = value; }
            }

            /// <summary>
            /// 构造函数
            /// </summary>
            public SQLHelper()
            {
                this.connectionString = ConfigHelper.GetAppSettings(Zhang.Model.DBReadWrite.ReadWrite);
            }

            /// <summary>
            /// 构造函数
            /// </summary>
            /// <param name="connectionString">数据库连接字符串</param>

            public SQLHelper(Zhang.Model.DBReadWrite key)
            {
                this.connectionString = ConfigHelper.GetAppSettings(key);
            }

            /// <summary>  
            /// 执行多条SQL语句，实现数据库事务。  yzc
            /// </summary>  
            /// <param name="ds">DataSet（row[0]为sql语句，row[1]是该语句的SqlParameter[]）</param>  
            public bool ExecuteSqlTran(DataSet ds)
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    if (conn.State != ConnectionState.Open)//如果数据库连接为关闭状态  
                        conn.Open();//打开数据库连接  

                    using (SqlTransaction trans = conn.BeginTransaction())//开始数据库事务  
                    {
                        SqlCommand cmd = new SqlCommand();//创建SqlCommand命令  
                        try
                        {
                            //循环  
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                string sql = row[0].ToString();
                                SqlParameter[] cmdParms = row[1] as System.Data.SqlClient.SqlParameter[];
                                PrepareCommand(cmd, conn, trans, sql, cmdParms); //调用PrepareCommand()函数，添加参数  
                                int val = cmd.ExecuteNonQuery();//调用增删改函数ExcuteNoQuery()，执行哈希表中添加的sql语句  
                                cmd.Parameters.Clear(); //清除参数  
                            }

                            trans.Commit();//提交事务  
                        }
                        catch //捕获异常  
                        {
                            trans.Rollback(); //事务回滚  
                            return false;
                            throw; //抛出异常  

                        }
                    }
                }
                return true;
            }
            /// <summary>
            /// 添加参数 yzc
            /// </summary>
            /// <param name="cmd"></param>
            /// <param name="conn"></param>
            /// <param name="trans"></param>
            /// <param name="cmdText"></param>
            /// <param name="cmdParms"></param>
            private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
            {
                if (conn.State != ConnectionState.Open)//如果数据库连接为关闭状态  
                    conn.Open();//打开数据库连接  
                cmd.Connection = conn;//设置命令连接  
                cmd.CommandText = cmdText;//设置执行命令的sql语句  
                if (trans != null)//如果事务不为空  
                    cmd.Transaction = trans;//设置执行命令的事务  
                cmd.CommandType = CommandType.Text;//设置解释sql语句的类型为“文本”类型（也是就说该函数不适用于存储过程）  
                if (cmdParms != null)//如果参数数组不为空  
                {
                    foreach (SqlParameter parameter in cmdParms) //循环传入的参数数组  
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value; //获取参数的值  
                        }
                        cmd.Parameters.Add(parameter);//添加参数  
                    }
                }
            }
            /// <summary>
            /// 执行SQL语句或存储过程，返回影响行数
            /// </summary>
            /// <param name="cmdType">SQL命令类型</param>
            /// <param name="cmdText">SQL语句或存储过程名称</param>
            /// <param name="sqlParam">SQL参数,可选</param>
            /// <returns>影响行数</returns>
            public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
            {
                SqlCommand sqlCmd;
                int result;

                sqlCmd = new SqlCommand();

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                    result = sqlCmd.ExecuteNonQuery();
                }

                sqlCmd.Dispose();
                return result;
            }

            /// <summary>
            /// 事务批量执行
            /// </summary>
            /// <param name="batchSQL">批量事务SQL</param>
            /// <param name="sqlParam">SQL参数变量，可选</param>
            public void ExecuteBatch(string batchSQL, params SqlParameter[] sqlParam)
            {
                SqlTransaction sqlTrans;
                SqlCommand sqlCmd;

                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                {
                    if (sqlConn.State != ConnectionState.Open)
                    {
                        sqlConn.Open();
                    }

                    sqlCmd = sqlConn.CreateCommand();
                    sqlTrans = sqlConn.BeginTransaction();
                    sqlCmd.Transaction = sqlTrans;
                    sqlCmd.Connection = sqlConn;
                    sqlCmd.CommandText = batchSQL;
                    sqlCmd.CommandType = CommandType.Text;

                    if (sqlParam != null && sqlParam.Length > 0)
                    {
                        sqlCmd.Parameters.AddRange(sqlParam);
                    }

                    try
                    {
                        sqlCmd.ExecuteNonQuery();
                        sqlTrans.Commit();
                    }
                    catch (Exception ex)
                    {
                        sqlTrans.Rollback();
                        sqlCmd.Dispose();
                        sqlConn.Dispose();
                        throw ex;
                    }

                    sqlCmd.Dispose();
                }
            }

            /// <summary>
            /// 执行SQL语句或存储过程，返回第一行第一列值
            /// </summary>
            /// <param name="cmdType">SQL命令类型</param>
            /// <param name="cmdText">SQL语句或存储过程名称</param>
            /// <param name="sqlParam">SQL参数,可选</param>
            /// <returns>结果值</returns>
            public object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
            {
                SqlCommand sqlCmd;
                object result;

                sqlCmd = new SqlCommand();
                result = null;

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                    result = sqlCmd.ExecuteScalar();
                }

                sqlCmd.Parameters.Clear();
                sqlCmd.Dispose();
                return result;
            }

            /// <summary>
            /// 执行存储过程或SQL语句返回数据表
            /// </summary>
            /// <param name="cmdType">SQL命令类型</param>
            /// <param name="cmdText">SQL语句或存储过程名称</param>
            /// <param name="sqlParam">SQL参数,可选</param>
            /// <returns>数据表</returns>
            public DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
            {
                SqlCommand sqlCmd;
                DataTable result;

                sqlCmd = new SqlCommand();
                result = new DataTable();

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                    result.Load(sqlCmd.ExecuteReader());
                }

                sqlCmd.Dispose();
                return result;
            }

            /// <summary>
            /// 执行存储过程或SQL语句返回数据集
            /// </summary>
            /// <param name="cmdType">SQL命令类型</param>
            /// <param name="cmdText">SQL语句或存储过程名称</param>
            /// <param name="sqlParam">SQL参数,可选</param>
            /// <returns>数据集</returns>
            public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
            {
                SqlCommand sqlCmd;
                SqlDataAdapter sqlAdapter;
                DataSet result;

                sqlCmd = new SqlCommand();
                result = new DataSet();

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                    sqlAdapter = new SqlDataAdapter(sqlCmd);
                    sqlAdapter.Fill(result);
                }

                sqlCmd.Dispose();
                sqlAdapter.Dispose();
                return result;
            }

            /// <summary>
            /// 执行存储过程或SQL语句返回SqlDataReader实例
            /// </summary>
            /// <param name="cmdType">SQL命令类型</param>
            /// <param name="cmdText">SQL语句或存储过程名称</param>
            /// <param name="sqlParam">SQL参数,可选</param>
            /// <returns>SqlDataReader实例</returns>
            public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
            {
                SqlCommand sqlCmd;
                SqlDataReader result;
                SqlConnection conn;

                conn = new SqlConnection(connectionString);
                sqlCmd = new SqlCommand();

                try
                {
                    PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                    result = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
                    sqlCmd.Parameters.Clear();
                    sqlCmd.Dispose();
                    return result;
                }
                catch
                {
                    sqlCmd.Dispose();
                    conn.Close();
                    throw;
                }
            }

            /// <summary>
            /// 执行存储过程或SQL语句返回SqlDataReader实例
            /// </summary>
            /// <param name="cmdType">SQL命令类型</param>
            /// <param name="cmdText">SQL语句或存储过程名称</param>
            /// <param name="sqlParam">SQL参数,可选</param>
            /// <returns>SqlDataReader实例</returns>
            public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, out SqlCommand com, params SqlParameter[] sqlParam)
            {
                SqlCommand sqlCmd;
                SqlDataReader result;
                SqlConnection conn;

                conn = new SqlConnection(connectionString);
                sqlCmd = new SqlCommand();

                try
                {
                    PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                    result = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
                    //sqlCmd.Parameters.Clear();
                    //sqlCmd.Dispose();
                    com = sqlCmd;
                    return result;
                }
                catch
                {
                    sqlCmd.Dispose();
                    conn.Close();
                    throw;
                }
            }



            public XmlReader ExecuteXmlReader(CommandType cmdType, string cmdText, params SqlParameter[] sqlParam)
            {
                SqlCommand sqlCmd;
                XmlReader myreader = null;
                SqlConnection conn;

                conn = new SqlConnection(connectionString);
                sqlCmd = new SqlCommand();

                try
                {
                    PrepareCommand(sqlCmd, conn, cmdType, cmdText, sqlParam);
                    myreader = sqlCmd.ExecuteXmlReader();
                    sqlCmd.Parameters.Clear();
                    sqlCmd.Dispose();
                    return myreader;
                }
                catch
                {
                    sqlCmd.Dispose();
                    conn.Close();
                    throw;
                }
            }

            /// <summary>
            /// 初始化SqlCommand参数
            /// </summary>
            /// <param name="sqlCmd"></param>
            /// <param name="conn"></param>
            /// <param name="cmdType"></param>
            /// <param name="cmdText"></param>
            /// <param name="sqlParam"></param>
            protected void PrepareCommand(SqlCommand sqlCmd, SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] sqlParam)
            {
            
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                sqlCmd.CommandTimeout = 180;
                sqlCmd.Connection = conn;
                sqlCmd.CommandText = cmdText;
                sqlCmd.CommandType = cmdType;

                if (sqlParam != null && sqlParam.Length > 0)
                {
                    sqlCmd.Parameters.AddRange(sqlParam);
                }
            }

            /// <summary>
            /// SqlBulkCopy 批量更新数据
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="list"></param>
            /// <param name="crateTemplateSql"></param>
            /// <param name="updateSql"></param>
            public void BulkUpdateData(DataTable dataTable, string crateTemplateSql, string updateSql)
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    using (var command = new SqlCommand("", conn))
                    {
                        try
                        {
                            conn.Open();
                            //数据库并创建一个临时表来保存数据表的数据
                            command.CommandText = $"  CREATE TABLE #TmpTable ({crateTemplateSql})";
                            command.ExecuteNonQuery();

                            //使用SqlBulkCopy 加载数据到临时表中
                            using (var bulkCopy = new SqlBulkCopy(conn))
                            {
                                foreach (DataColumn dcPrepped in dataTable.Columns)
                                {
                                    bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                                }

                                bulkCopy.BulkCopyTimeout = 660;
                                bulkCopy.DestinationTableName = "#TmpTable";
                                bulkCopy.WriteToServer(dataTable);
                                bulkCopy.Close();
                            }

                            // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
                            command.CommandTimeout = 300;
                            command.CommandText = updateSql;
                            command.ExecuteNonQuery();
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }


            /// <summary>
            /// SqlBulkCopy 批量插入数据
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="list"></param>
            /// <param name="tableName"></param>
            public void BulkInsertData(DataTable dataTable, string tableName)
            {
                using (var bulkCopy = new SqlBulkCopy(connectionString))
                {
                    foreach (DataColumn dcPrepped in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                    }

                    bulkCopy.BulkCopyTimeout = 660;
                    bulkCopy.DestinationTableName = tableName;
                    bulkCopy.WriteToServer(dataTable);
                    bulkCopy.Close();
                }
            }
        }
    }
}